package com.cskaoyan;

import com.cskaoyan.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * 创建日期: 2022/04/29 20:48
 *
 * @author ciggar
 */
public class Work1 {

    static Connection connection;
    static Statement statement;

    static {

        try {
            connection = JDBCUtils.getConnection();
            statement = connection.createStatement();

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {
//        sql01();
//        sql02();
//        sql03();
//        sql04();
//        sql05();
        sql06();
    }


//    -- 1.请查出每个导师所带研究生的姓名。
    public static void sql01() throws SQLException {

        ResultSet resultSet = statement.executeQuery("select t.name,group_concat(s.name) as sname from t_staff as t inner join t_stu as s on t.stfid = s.tutorid group by t.stfid");

        while (resultSet.next()) {
            String name = resultSet.getString("name");
            String sname = resultSet.getString("sname");
            System.out.println("name:" + name +",sname:" + sname);
        }
        JDBCUtils.close(null,null,resultSet);

    }

//    	2.清查出特定姓名的导师所带研究生的姓名。
    public static void sql02() throws SQLException {
        ResultSet resultSet = statement.executeQuery("select t.name,group_concat(s.name) as sname from t_staff as t inner join t_stu as s on t.stfid = s.tutorid and t.name = '邹华'");
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            String sname = resultSet.getString("sname");
            System.out.println("name:" + name +",sname:" + sname);
        }
        JDBCUtils.close(null,null,resultSet);
    }

//    	3.请查出每个导师所带研究生的数量。
    public static void sql03() throws SQLException {
        ResultSet resultSet = statement.executeQuery("select t.name,count(1)  as stuNumber from t_staff as t inner join t_stu as s on t.stfid = s.tutorid group by t.stfid");
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            Integer stuNumber = resultSet.getInt("stuNumber");
            System.out.println("name:" + name +",stuNumber:" + stuNumber);
        }
        JDBCUtils.close(null,null,resultSet);

    }


//    	4.请查出每个导师所带的男研究生的数量。
    public static void sql04() throws SQLException {
        String sql = "select t.name,count(s.stuid) as stuNumber from t_staff as t inner join t_stu as s on t.stfid = s.tutorid and s.gender = 'male' group by t.stfid";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String name = resultSet.getString("name");
            Integer stuNumber = resultSet.getInt("stuNumber");
            System.out.println("name:" + name +",stuNumber:" + stuNumber);
        }
        JDBCUtils.close(null,null,resultSet);

    }

//    	5.请找出选择哪个研究方向的导师最多。
    public static void sql05() throws SQLException {

        String sql = "select direction,count(1) as tutor_number from t_staff group by direction order by tutor_number desc limit 1";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String direction = resultSet.getString("direction");
            Integer tutorNumber = resultSet.getInt("tutor_number");
            System.out.println("direction:" + direction +",tutorNumber:" + tutorNumber);
        }
        JDBCUtils.close(null,null,resultSet);

    }

//    	6.请找统计不同职称的导师的个数。
    public static void sql06() throws SQLException {

        String sql = "select title,count(1) as tutor_number,group_concat(name) from t_staff group by title";
        ResultSet resultSet = statement.executeQuery(sql);
        while (resultSet.next()) {
            String title = resultSet.getString("title");
            Integer tutorNumber = resultSet.getInt("tutor_number");
            System.out.println("title:" + title +",tutorNumber:" + tutorNumber);
        }
        JDBCUtils.close(null,null,resultSet);

    }

}
